﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DataService
/// </summary>
public class DataService
{
    private string _connectionstring = "";
    private SqlConnection _conn = null;

    public string ConnectionString
    {
        get
        {
            return _connectionstring;
        }
        set
        {
            _connectionstring = value;
        }
    }

    public int GetIdentityInsertValue(string table)
    {
        SqlCommand _command = new SqlCommand(string.Format("SELECT IDENT_CURRENT('{0}')", table), _conn);

        return int.Parse(_command.ExecuteScalar().ToString());

    }
    public DataService()
    {
        _connectionstring = Configuration.GetConnectionString();
    }

    public void Connect()
    {
        try
        {
            _conn = new SqlConnection(_connectionstring);
            _conn.Open();
        }
        catch (Exception e)
        {
            throw new Exception("Không kết nối được cơ sở dữ liệu !!! " + e.Message);
        }
    }

    public void Disconnect()
    {
        _conn.Close();
        _conn = null;
    }

    public bool IsConnect()
    {
        return (_conn != null);
    }

    public SqlDataReader ExecuteQuery(string strSQL)
    {
        SqlCommand _command = new SqlCommand(strSQL, _conn);

        return _command.ExecuteReader();

    }

    public object ExecuteScalar(string strSQL)
    {
        SqlCommand _command = new SqlCommand(strSQL, _conn);

        return _command.ExecuteScalar();
    }

    public int ExecuteNoneQuery(string strSQL)
    {

        SqlCommand _command = new SqlCommand(strSQL, _conn);
        int i = _command.ExecuteNonQuery();
        return i;
    }

    public DataSet ExcuteQueryDataSet(string strSQL)
    {
        SqlDataAdapter adapter = new SqlDataAdapter(strSQL, _conn);
        DataSet ds = new DataSet("DATASET");
        adapter.Fill(ds);
        return ds;
    }
    public DataTable ExcuteQueryDataTable(string strSQL)
    {
        DataSet ds = ExcuteQueryDataSet(strSQL);
        return ds.Tables[0];
    }

    public int ExcuteProcedureWithReturn(string procName, SqlParameter[] parameters)
    {
        try
        {
            SqlCommand command = new SqlCommand();

            command.Connection = _conn;

            command.CommandText = procName;

            command.CommandType = CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (SqlParameter para in parameters)
                {
                    command.Parameters.Add(para);
                }
            }

            command.Parameters.Add(new SqlParameter("@returnVal", SqlDbType.Int));
            command.Parameters["@returnVal"].Direction = ParameterDirection.Output;
            command.ExecuteNonQuery();
            int intDangNhap = int.Parse(command.Parameters["@returnVal"].Value.ToString());
            return intDangNhap;
        }
        catch (Exception ex)
        {
            throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() \n" + ex.Message);
        }
    }

    public DataTable ExcuteProcedureGetTable(string procName, SqlParameter[] parameters)
    {
        try
        {

            SqlCommand command = new SqlCommand();

            command.Connection = _conn;

            command.CommandText = procName;

            command.CommandType = CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (SqlParameter para in parameters)
                {
                    command.Parameters.Add(para);
                }
            }

            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet ds = new DataSet("DATASET");
            adapter.Fill(ds);
            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
        }
    }

    public DataSet ExcuteProcedureGetDataSet(string procName, SqlParameter[] parameters)
    {
        try
        {

            SqlCommand command = new SqlCommand();

            command.Connection = _conn;

            command.CommandText = procName;

            command.CommandType = CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (SqlParameter para in parameters)
                {
                    command.Parameters.Add(para);
                }
            }

            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet ds = new DataSet("DATASET");
            adapter.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
        }
    }

    public void ExcuteProcedure(string procName, SqlParameter[] parameters)
    {
        try
        {

            SqlCommand command = new SqlCommand();

            command.Connection = _conn;

            command.CommandText = procName;

            command.CommandType = CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (SqlParameter para in parameters)
                {
                    command.Parameters.Add(para);
                }
            }

            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new Exception("Lỗi function DataService.ExcuteProcedure()!!! \n" + ex.Message);
        }
    }

    public DataSet ShowData(string name_proc)
    {
        SqlParameter[] parameters = null;
        this.Connect();        
        DataSet dataset =this.ExcuteProcedureGetDataSet(name_proc, parameters);
        this.Disconnect();
        return dataset;
    }
}
